﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;
using System.Data.SQLite;

namespace TP2
{
    public partial class TP2 : Form
    {
        public TP2()
        {
            InitializeComponent();
            this.assignTooltips();
            int i = this.checkForDB();
            this.yearComboBox1.DropDownStyle = ComboBoxStyle.DropDownList;
            this.yearComboBox2.DropDownStyle = ComboBoxStyle.DropDownList;
        }

        private void assignTooltips()
        {
            this.generateTooltip(this.generateDBButton, "Generates Database through the CSV files present on ./rebcsv");

            this.generateTooltip(this.yearComboBox1, "Select Year");

            this.generateTooltip(this.yearComboBox2, "Select Year");
        }

        private void generateTooltip(Button button, String description)
        {
            ToolTip tooltip = new ToolTip();
            tooltip.ShowAlways = true;
            tooltip.SetToolTip(button, description);
        }

        private void generateTooltip(ComboBox box, String description)
        {
            ToolTip tooltip = new ToolTip();
            tooltip.ShowAlways = true;
            tooltip.SetToolTip(box, description);
        }

        private int checkForDB()
        {
            if (File.Exists("TP2.db"))
            {
                this.ContentsSplitContainer.Enabled = true;
                return 1;
            }
            else
            {
                return 0;
            }
        }

        private void generateDBButton_Click(object sender, EventArgs e)
        {
            bool firstLineFlag = true;
            bool quotationFlag = false;

            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = "Data Source=TP2.db;New=True;Compress=True;Synchronous=Off";
            conn.Open();

            for (int i = 0; i < 1; i++)
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandType = CommandType.Text;
                cmd.Connection = conn;

                cmd.CommandText = String.Format("drop table if exists reb{0}", i);
                cmd.ExecuteNonQuery();

                cmd.CommandText = String.Format("create table if not exists reb{0} (cod_docent integer, nome text, ult_grau text, curso text, cat text,regime text, ano_lectivo integer, tipo_estabelecimento text, nome_estabelecimento text)", i);
                cmd.ExecuteNonQuery();

                StreamReader sr = new StreamReader("./rebcsv/reb0.csv");

                string strline = "";
                string[] _values = null;
               
                while (!sr.EndOfStream)
                {
                    strline = sr.ReadLine();

                    if (firstLineFlag == false)
                    {
                        if (strline.Contains("\""))
                        {
                            while ((strline.Split('\"').Length % 2) == 0)
                            {
                                strline = strline + sr.ReadLine();
                            }

                            string[] quots = strline.Split('\"');
                            strline = "";

                            for (int j = 0; j < quots.Length; j++)
                            {
                                if ((j % 2) == 1)
                                {
                                    quots[j] = quots[j].Replace(',', '|');
                                }

                                strline = strline + quots[j];
                            }

                            quotationFlag = true;
                        }

                        while (strline.Split(',').Length < 9)
                        {
                            strline = strline + sr.ReadLine();
                        }

                        strline = strline.Replace("\"", "");

                        _values = strline.Split(',');

                        if (quotationFlag == true)
                        {
                            for (int j = 0; j < _values.Length; j++)
                            {
                                if (_values[j].Contains("|"))
                                {
                                    _values[j] = _values[j].Replace('|', ',');
                                }
                            }

                            quotationFlag = false;
                        }

                        if (_values[1].Equals("Abel Duarte de Oliveira"))
                        {
                            Console.WriteLine(strline);
                        }

                        cmd.CommandText = String.Format("insert into reb{0} values (\"{1}\",\"{2}\",\"{3}\",\"{4}\",\"{5}\",\"{6}\",{7},\"{8}\",\"{9}\")", i, _values[0],
                            _values[1], _values[2], _values[3], _values[4], _values[5], _values[6], _values[7], _values[8]);
                        cmd.ExecuteNonQuery();

                    }
                    else
                    {
                        firstLineFlag = false;
                    }
                }

                sr.Close();

                firstLineFlag = true;
            }

            conn.Close();

            this.ContentsSplitContainer.Enabled = true;
        }

        private void yearComboBox1_YearSelected(object sender, EventArgs e)
        {
            this.listView1.Items.Clear();

            string t = this.yearComboBox1.SelectedItem.ToString();
            char c = t[t.Length - 1];

            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = "Data Source=TP2.db;New=False;Compress=True;Synchronous=Off";
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            cmd.CommandText = String.Format("select * from reb{0}", c);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Dictionary<string, Dictionary<string, int>> d = new Dictionary<string, Dictionary<string, int>>();

            while (reader.Read())
            {
                String s = reader[8].ToString();
                String z = reader[4].ToString();

                if (d.ContainsKey(s))
                {
                    if (d[s].ContainsKey(z))
                    {
                        d[s][z] = d[s][z] + 1;
                    }
                    else
                    {
                        d[s].Add(z, 1);
                    }
                }
                else
                {
                    Dictionary<string, int> b = new Dictionary<string, int>();
                    b.Add(z, 1);
                    d.Add(s, b);
                }
            }

            foreach (KeyValuePair<string, Dictionary<string, int>> keypair in d)
            {
                foreach (KeyValuePair<string, int> innerkeypair in keypair.Value)
                {
                    String[] arr = { keypair.Key, innerkeypair.Key, innerkeypair.Value.ToString() };
                    ListViewItem item = new ListViewItem(arr);
                    this.listView1.Items.Add(item);
                }
            }

            this.listView1.Sorting = SortOrder.Ascending;

            this.Establishment1.Width = -1;
            this.Category.Width = -1;
            this.Teachers1.Width = -2;

            conn.Close();
        }

        private void yearComboBox2_YearSelected(object sender, EventArgs e)
        {
            this.listView2.Items.Clear();

            string t = this.yearComboBox2.SelectedItem.ToString();
            char c = t[t.Length - 1];

            SQLiteConnection conn = new SQLiteConnection();
            conn.ConnectionString = "Data Source=TP2.db;New=False;Compress=True;Synchronous=Off";
            conn.Open();

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.CommandType = CommandType.Text;
            cmd.Connection = conn;

            cmd.CommandText = String.Format("select * from reb{0}", c);
            SQLiteDataReader reader = cmd.ExecuteReader();

            Dictionary<string, Dictionary<string, int>> d = new Dictionary<string, Dictionary<string, int>>();

            while (reader.Read())
            {
                String s = reader[8].ToString();
                String z = reader[2].ToString();

                if (d.ContainsKey(s))
                {
                    if (d[s].ContainsKey(z))
                    {
                        d[s][z] = d[s][z] + 1;
                    }
                    else
                    {
                        if (z.Equals("Licenciatura") || z.Equals("Mestrado") || z.Equals("Doutoramento"))
                        {
                            d[s].Add(z, 1);
                        }
                    }
                }
                else
                {
                    if (z.Equals("Licenciatura") || z.Equals("Mestrado") || z.Equals("Doutoramento"))
                    {
                        Dictionary<string, int> b = new Dictionary<string, int>();
                        b.Add(z, 1);
                        d.Add(s, b);
                    }
                }
            }

            foreach (KeyValuePair<string, Dictionary<string, int>> keypair in d)
            {
                foreach (KeyValuePair<string, int> innerkeypair in keypair.Value)
                {
                    String[] arr = { keypair.Key, innerkeypair.Key, innerkeypair.Value.ToString() };
                    ListViewItem item = new ListViewItem(arr);
                    this.listView2.Items.Add(item);
                }
            }

            this.listView2.Sorting = SortOrder.Ascending;

            this.Establishment2.Width = -1;
            this.Degree.Width = -1;
            this.Teachers2.Width = -2;

            conn.Close(); 
        }
    }
}